﻿/// File: DbColumn.cs 
/// Date: 03/15/2012
/// Author: Phil Allison (stoneguard@gmail.com)
/// Purpose: These are general support functions wrapped within a class in the base namespace for this library
/// 
/// copyright (c) 2001-2012, Phil Allison. Under the MIT License http://www.opensource.org/licenses/mit-license.php  
/// Feel free to reuse and modify this code as long as credit is given to the original author.
/// Modified code must be noted in this section.
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace sgsBase
{
	[Serializable]
	public class DbColumns
	{
		protected List<DbColumn> _columns = new List<DbColumn>();
		protected int _pkCount = 0;
		protected bool _excludeNullFields = false; // used to exclude NULL values from where clause

		~DbColumns()
		{
			_columns.Clear();
			_columns = null;
		}

		public int PrimaryKeyCount
		{
			get { return _pkCount; }
		}

		public bool ExcludeNullFields
		{
			get { return _excludeNullFields; }
			set { _excludeNullFields = value; }
		}

		#region Table Columns
		/// <summary>
		/// Adds name only, assuming that data type is VarChar, is not a key, and is not auto-increment
		/// </summary>
		/// <param name="name"></param>
		public void AddColumn(string name, bool nullable = true)
		{
			DbColumn dbc = new DbColumn(name);
			dbc.Nullable = nullable;
			_columns.Add(dbc);
		}
		public void AddColumn(string name, SqlDbType dbType, bool nullable = true)
		{
			DbColumn dbc = new DbColumn(name, dbType);
			dbc.Nullable = nullable;
			_columns.Add(dbc);
		}
		public void AddColumn(string name, SqlDbType dbType, bool isKey, bool autoIncrement, string value="")
		{
			DbColumn dbc = new DbColumn(name, dbType, isKey, autoIncrement);
			if (isKey)
				_pkCount++;
			dbc.Value = value;
			_columns.Add(dbc);
		}
		public void AddColumn(DbColumn dbc)
		{
			_columns.Add(dbc);
			if (dbc.PrimaryKey)
				_pkCount++;
		}
		#endregion

		/// <summary>
		/// AddValue Adds the value given to the specific database column
		/// </summary>
		/// <param name="ColumnName">The datbase column name.</param>
		/// <param name="Value">The value to set the column to.</param>
		public void AddValue(string columnName, object value)
		{
			DbColumn dbc = _columns.Find(delegate(DbColumn db) { return db.ColumnName == columnName; });

			if (dbc != null)
			{
				if (value.Equals(System.DBNull.Value))
				{
					dbc.Value = "";
				}
				else if (dbc.DataType == SqlDbType.DateTime)
				{
					dbc.Value = ((DateTime)value).ToString("yyyy-MM-dd HH:mm:ss.fff");
				}
				else
				{
					dbc.Value = value.ToString();
				}
			}
		}

		public void ClearValues()
		{
			_columns.ForEach(delegate(DbColumn dbc)
			{
				dbc.Value = "";
			});
		}

		/// <summary>
		/// Copy the columns to a new DbColumns object
		/// </summary>
		/// <returns></returns>
		public DbColumns Copy()
		{
			DbColumns dbNew = new DbColumns();

			_columns.ForEach(delegate(DbColumn dbc)
			{
				dbNew.AddColumn(dbc.ColumnName, dbc.DataType, dbc.PrimaryKey, dbc.AutoIncrement, dbc.Value);
			});

			return dbNew;
		}

		/// <summary>
		/// Returns a where clause based on the columns which have been assigned values. 
		/// </summary>
		/// <returns></returns>
		public string LimitingValues(bool primaryKeys = false, bool multiRows = true)
		{
			StringBuilder sb = new StringBuilder(); // ret = "";

			_columns.ForEach(delegate(DbColumn dbc)
			{
                if (primaryKeys == false || dbc.PrimaryKey)
                {
                    if (dbc.Value != "" || !multiRows)
                    {
						sb.Append(" and ");
						sb.Append(dbc.ColumnName);
						sb.Append(dbc.ConvertClause());
                    }
                }
			});

			if (sb.Length > 4)
				return sb.ToString().Substring(4); // trim out leading ' and'

			return "";
		}

		public List<string> ListedValues()
		{
			List<string> ret = new List<string>();

			_columns.ForEach(delegate(DbColumn dbc)
			{
				if (dbc.Value != "")
				{
					if (dbc.Value.Contains("%"))
					{
						ret.Add(dbc.ColumnName + " like " + dbc.ConvertValue());
					}
					else
					{
						if (dbc.DataType == SqlDbType.DateTime)
						{
							string dbsql = " between " + DbInterface.quote(CASTHelper.DateISO(dbc.Value));
							dbsql += " and '" + CASTHelper.DateISO(dbc.Value) + " 23:59'";
							ret.Add(dbc.ColumnName + dbsql);
						}
						else
							ret.Add(dbc.ColumnName + dbc.ConvertClause()); 
					}
				}
			});

			return ret;
		}

		public string GetValuePairs()
		{
			StringBuilder sb = new StringBuilder();

			_columns.ForEach(delegate(DbColumn dbc)
			{
				if (dbc.PrimaryKey == false)
				{
					string val = dbc.ConvertValue();

					if (!ExcludeNullFields || val != "NULL")
					{
						sb.Append(dbc.ColumnName);
						sb.Append("=");
						sb.Append(val);
						sb.Append(",");
					}
				}
			});

			return sb.ToString().Trim(',');
		}

		public bool GetNamesAndVals(ref string cols, ref string vals)
		{
            StringBuilder sbc = new StringBuilder();
            StringBuilder sbv = new StringBuilder();
			//string bcols = "";
			//string bvals = "";

			_columns.ForEach(delegate(DbColumn dbc)
			{
				if (dbc.PrimaryKey == false || dbc.AutoIncrement == false)
				{
					sbc.Append(dbc.ColumnName);
                    sbc.Append(",");
					//cols += dbc.ColumnName + ",";
					sbv.Append(dbc.ConvertValue());
					sbv.Append(",");
				}
			});

			cols = sbc.ToString().Trim(',');
			vals = sbv.ToString().Trim(',');

            sbc.Clear();
            sbv.Clear();
			return true;
		}

		// if there is only 1 column as primary key, then use it to set the proper column
		public bool IncrementPK(int ret)
		{
			if (PrimaryKeyCount != 1)
				return false;

			_columns.ForEach(delegate(DbColumn dbc)
			{
				if (dbc.PrimaryKey && dbc.AutoIncrement)
				{
					dbc.ValueInt = ret;
				}
			});

			return true;
		}

		public void LoadValues(DataRow dr, DataColumnCollection dcc)
		{
			ClearValues(); // just in case a passed in value is null but the preceeding one was not.
			foreach (DataColumn dc in dcc)
			{
				AddValue(dc.ColumnName, dr[dc.ColumnName]);
			}
		}
		#region database Column Value Conversions
		/// <summary>
		/// indicates whether the column count is valid
		/// </summary>
		/// <param name="col"></param>
		/// <returns></returns>
		public bool valid(int col)
		{
			return (_columns.Count > col);
		}

		public string StringAt(int col)
		{
			return valid(col) ? _columns[col].Value : "";
		}

		public int IntAt(int col)
		{
			return valid(col) ? (int)_columns[col].ValueInt : 0;
		}

		public long LongAt(int col)
		{
			return valid(col) ? _columns[col].ValueLong : 0;
		}

		public bool BoolAt(int col)
		{
			return valid(col) ? _columns[col].ValueBool : false;
		}

		public decimal DecimalAt(int col)
		{
			return valid(col) ? _columns[col].ValueDecimal : 0;
		}

		public double DoubleAt(int col)
		{
			return valid(col) ? _columns[col].ValueDouble : 0;
		}

		public DateTime DateAt(int col)
		{
			return valid(col) ? _columns[col].ValueDate : DateTime.MinValue;
		}

		public DateTime DateTimeAt(int col)
		{
			return valid(col) ? _columns[col].ValueDate : DateTime.MinValue;
		}

		public DateTime DateTimeTSAt(int col)
		{
			return valid(col) ? _columns[col].ValueDateTime : DateTime.MinValue;
		}
		public void SetValAt(int col, int val)
		{
			if (valid(col))
				_columns[col].ValueInt = val;
		}
		public void SetValAt(int col, long val)
		{
			if (valid(col))
				_columns[col].ValueLong = val;
		}
		public void SetValAt(int col, string val)
		{
			if (valid(col))
				_columns[col].Value = val;
		}
		public void SetValAt(int col, decimal val)
		{
			if (valid(col))
				_columns[col].ValueDecimal = val;
		}
		public void SetValAt(int col, double val)
		{
			if (valid(col))
				_columns[col].ValueDouble = val;
		}
		public void SetValAt(int col, DateTime val, bool IsTS)
		{
			if (!valid(col))
				return;

			if (val.Year < 1900)
				return;
			if (IsTS)
				_columns[col].ValueDateTime = val;
			else
				_columns[col].ValueDate = val;
		}
		public void SetValAt(int col, DateTime val)
		{
			SetValAt(col, val, false);
		}
		public void SetValAt(int col, bool val)
		{
			if (valid(col))
				_columns[col].ValueBool = val;
		}

		public int Count
		{
			get
			{
				return _columns.Count;
			}
		}
		#endregion

		public int IndexOf(string columnName)
		{
			int i = 0;

			i = _columns.FindIndex(delegate(DbColumn dbc)
			{
				return dbc.ColumnName == columnName;
			});

			return i;
		}
	}
	/// <summary>
	/// DbColumn defines a database column used in DataObject class
	/// </summary>
	[Serializable]
	public class DbColumn
	{
		public string ColumnName = "";
		public SqlDbType DataType = SqlDbType.VarChar;
		public bool PrimaryKey = false;
		public bool AutoIncrement = false;
		private string _value = ""; // all values are converted to a common string value. This can help when building actual queries. 
		public bool Nullable = true;

		public DbColumn(string name, SqlDbType dbType=SqlDbType.VarChar, bool isKey=false, bool identity=false)
		{
			init(name, dbType, isKey, identity);
		}

		#region Value And Conversion functions
		public string Value
		{
			get { return _value.Trim(); }
			set { _value = value; }
		}

		public int ValueInt
		{
			get
			{
				int val = 0;
				try
				{
					val = CASTHelper.Integer(_value);
				}
				catch { }

				return val;
			}
			set { _value = value.ToString(); }
		}

		public long ValueLong
		{
			get
			{
				long val = 0;
				try
				{
					val = CASTHelper.Long(_value);
				}
				catch { }

				return val;
			}
			set { _value = value.ToString(); }
		}

		public bool ValueBool
		{
			get
			{
				return CASTHelper.Boolean(_value);
			}
			set
			{
				_value = value.ToString();
			}
		}
		public DateTime ValueDate
		{
			get
			{
				DateTime val = new DateTime();
				try
				{
					val = DateTime.Parse(ValueDateTime.ToShortDateString());
				}
				catch { }

				return val;
			}
			set
			{
				_value = value.ToShortDateString();
				if (ValueDate.Year < 1900)
					_value = "";
			}
		}
		public DateTime ValueDateTime
		{
			get
			{
				DateTime val = new DateTime();
				try
				{
					if (_value != "")
						val = DateTime.Parse(_value);
				}
				catch { }

				return val;
			}
			set
			{
				_value = value.ToString("yyyy-MM-dd HH:mm:ss.fff");
				if (ValueDate.Year < 1900)
					_value = "";
			}
		}

		public Decimal ValueDecimal
		{
			get
			{
				Decimal val = new Decimal(0);
				try
				{
					val = CASTHelper.Decimal(_value);
				}
				catch { }

				return val;
			}
			set
			{
				_value = value.ToString();
			}
		}
		public Double ValueDouble
		{
			get
			{
				Double val = 0;
				try
				{
					val = CASTHelper.Double(_value);
				}
				catch { }

				return val;
			}
			set
			{
				_value = value.ToString();
			}
		}
		#endregion

		public string ConvertClause()
		{
			string ret = ConvertValue();

			if (ret == "NULL")
				return " is NULL";

			if (Value.Contains("%"))
				return " like " + ret;

			return " = " + ret;
		}
		/// <summary>
		/// Converts the stored value into a string based value for database functions
		/// </summary>
		/// <returns></returns>
		public string ConvertValue()
		{
			string ret = "";

			if (_value == "" || _value == null || _value == "[NULL]")
			{
				if (Nullable)
					return "NULL";
			}

			if (DataType == SqlDbType.DateTime)
			{
				if (DateTime.Parse(_value) == DateTime.MinValue)
				{
					if (Nullable)
						return "NULL";
				}
				return DbInterface.quote(DateTime.Parse(_value).ToString("yyyy-MM-dd HH:mm:ss.fff")); //.ToShortDateString());
			}
			if (DataType == SqlDbType.Date)
			{
				if (DateTime.Parse(_value) == DateTime.MinValue)
				{
					if (Nullable)
						return "NULL";
				}
				DateTime dt = DateTime.Parse(_value);

				return DbInterface.quote(dt.ToShortDateString()); //.ToShortDateString());
			}

			if (DataType == SqlDbType.Int || DataType == SqlDbType.SmallInt)
			{
				int result = 0;
				if (Int32.TryParse(_value, out result))
					return _value;

				return "0";
			}

			if (DataType == SqlDbType.BigInt)
			{
				long result = 0;
				if (Int64.TryParse(_value, out result))
					return _value;

				return "0";
			}

			if (DataType == SqlDbType.Money || DataType == SqlDbType.Float || DataType == SqlDbType.Decimal)
			{

				decimal result = 0;
				if (Decimal.TryParse(_value, out result))
					return _value;

				return "0";
			}

			// at this point the value has fallen through and is assumed to be a varchar or text type
			ret = DbInterface.quote(_value);

			return ret;
		}

		#region private functions
		private void init(string name, SqlDbType dbType, bool isKey, bool autoIncrement)
		{
			ColumnName = name;
			DataType = dbType;
			PrimaryKey = isKey;
			AutoIncrement = autoIncrement;
		}

		#endregion
	}
}
